package com.weiweiqin.utils;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import com.weiweiqin.model.UserMobileSource;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

public class ReadExcelUtils {

    public static void main(String[] args) {
        long t = System.currentTimeMillis();
        // ArrayList<ArrayList<Object>> result = new
        // ExcelUtil().readExcel("D://工作表.xlsx");
        ArrayList<ArrayList<Object>> result = new ReadExcelUtils().readExcelByColumn("D://order.xls", new int[] { 1, 3, 4,6});
        System.out.println(System.currentTimeMillis() - t);
        for (int i = 0; i < result.size(); i++) {
            for (int j = 0; j < result.get(i).size(); j++) {
                String column = result.get(i).get(j).toString();
                UserMobileSource userMobileSource = new UserMobileSource();
                if(j == 0) {
                    userMobileSource.setPinfo(column);
                }else if(j == 1) {
                    userMobileSource.setName(column);
                }else if(j == 2) {
                    userMobileSource.setMobile(column);
                }else if(j == 3) {
                    userMobileSource.setAddr(column);
                }
                System.out.print("[" + result.get(i).get(j).toString() + "]\t");
            }
            System.out.println("");
        }
    }

    public ArrayList<ArrayList<Object>> readExcel(String fileName) {
        ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();
        ArrayList<Object> colList;
        try {
            Workbook wb = initWorkBook(fileName);
            Sheet sheet = wb.getSheetAt(0);
            Row row = null;
            Cell cell = null;
            for (int i = sheet.getFirstRowNum(), rowCount = 0; rowCount < sheet.getPhysicalNumberOfRows(); i++) {
                row = sheet.getRow(i);
                colList = new ArrayList<Object>();
                if (row == null) {
                    if (i != sheet.getPhysicalNumberOfRows()) {
                        rowList.add(colList);
                    }
                    continue;
                } else {
                    rowCount++;
                }
                for (int j = row.getFirstCellNum(); j <= row.getLastCellNum(); j++) {
                    cell = row.getCell(j);
                    if (cell == null) {
                        if (j != row.getLastCellNum()) {
                            colList.add("");
                        }
                        continue;
                    }
                    colList.add(readColValue(cell));
                }
                rowList.add(colList);
            }
            return rowList;
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
        return null;
    }

    public ArrayList<ArrayList<Object>> readExcelByColumn(String fileName, int[] colId) {
        ArrayList<ArrayList<Object>> rowList = new ArrayList<ArrayList<Object>>();
        ArrayList<Object> colList;
        try {
            Workbook wb = initWorkBook(fileName);
            Sheet sheet = wb.getSheetAt(0);
            Row row = null;
            Cell cell = null;
            for (int i = sheet.getFirstRowNum(), rowCount = 0; rowCount < sheet.getPhysicalNumberOfRows(); i++) {
                row = sheet.getRow(i);
                colList = new ArrayList<Object>();
                if (row == null) {
                    if (i != sheet.getPhysicalNumberOfRows()) {
                        rowList.add(colList);
                    }
                    continue;
                } else {
                    rowCount++;
                }
                for (int j = 0; j < colId.length; j++) {
                    cell = row.getCell(colId[j]);
                    // here different from read all ! as no end limit.
                    if (cell == null) {
                        colList.add("");
                        continue;
                    }
                    colList.add(readColValue(cell));
                }
                rowList.add(colList);
            }
            return rowList;
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
        return null;

    }

    private String readColValue(Cell cell) {
        String value = "";
        switch (cell.getCellTypeEnum()) {
            case STRING:
                value = cell.getStringCellValue();
                break;
            case NUMERIC:
                short format = cell.getCellStyle().getDataFormat();
                if (format == 0) {
                    DecimalFormat df = new DecimalFormat("#");
                    value = df.format(cell.getNumericCellValue());
                } else {
                    SimpleDateFormat sdf = null;
                    if (format == 14 || format == 31 || format == 57 || format == 58) {
                        sdf = new SimpleDateFormat("yyyy-MM-dd");
                    } else if (format == 20 || format == 32) {
                        sdf = new SimpleDateFormat("HH:mm");
                    }
                    Date date = DateUtil.getJavaDate(cell.getNumericCellValue());
                    value = sdf.format(date);
                }
                break;
            case BOOLEAN:
                value = String.valueOf(Boolean.valueOf(cell.getBooleanCellValue()));
                break;
            case BLANK:
                value = "";
                break;
            default:
                value = cell.toString();
        }
        return value;
    }

    private Workbook initWorkBook(String fileName) throws IOException {
        File file = new File(fileName);
        InputStream is = new FileInputStream(file);
        Workbook workbook = null;
        if (fileName.endsWith(".xls")) {
            workbook = new HSSFWorkbook(is);
        } else if (fileName.endsWith(".xlsx")) {
            workbook = new XSSFWorkbook(is);
        }
        return workbook;
    }
}